package ro.gateway.aida.admin;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Iterator;

import javax.servlet.ServletContext;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.servlet.Constants;

/**
 * Created by IntelliJ IDEA.
 * User: smike
 * Date: Apr 19, 2004
 * Time: 8:08:10 PM
 * To change this template use File | Settings | File Templates.
 */
public class PropertyListDB extends DBPersistenceManager {
	private PropertyListDB(PersistenceToken token) {
		super(token);
	}

	public static PropertyListDB getManager(PersistenceToken token) {
		return new PropertyListDB(token);
	}

	public static PropertyListDB getManager(ServletContext application) {
		return new PropertyListDB(PersistenceManager.tokenLookup(application));
	}

	public PropertyList getList(String list_name)
		 throws SQLException {
		if (list_name == null) return null;
		Connection con = getConnection();

		PropertyList result = null;
		try {
			PreparedStatement ps = con.prepareStatement(GET_LIST);
			ps.setString(1, list_name);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = new PropertyList();
				result.setId(rs.getLong(1));
				result.setList_name(rs.getString(2));
				result.setDisplay_name_tr_item(rs.getString(3));
				result.setList_title_tr_item(rs.getString(4));
				result.setItem_title_tr_item(rs.getString(5));
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (result != null) {
			result.setItems(getListItems(result.getId()));
		}

		return result;
	}

	public PropertyList getListById(long id, boolean get_items)
		 throws SQLException {
		Connection con = getConnection();

		PropertyList result = null;
		try {
			PreparedStatement ps = con.prepareStatement(GET_LIST_BY_ID);
			ps.setLong(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = new PropertyList();
				result.setId(rs.getLong(1));
				result.setList_name(rs.getString(2));
				result.setDisplay_name_tr_item(rs.getString(3));
				result.setList_title_tr_item(rs.getString(4));
				result.setItem_title_tr_item(rs.getString(5));
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		if (get_items && result != null) {
			result.setItems(getListItems(result.getId()));
		}

		return result;
	}

	private ArrayList getListItems(long list_id) throws SQLException {
		Connection con = getConnection();

		ArrayList result = new ArrayList();
		try {
			PreparedStatement ps = con.prepareStatement(GET_ITEMS_FOR_LIST);
			ps.setLong(1, list_id);
			ResultSet rs = ps.executeQuery();
			PropertyListItem last_item = null;
			while (rs.next()) {
				long id = rs.getLong(1);
				if (last_item == null || last_item.getId() != id) {
					// new item
					last_item = new PropertyListItem();
					last_item.setId(id);
					last_item.setList_id(list_id);
					result.add(last_item);
				}
				last_item.setString(rs.getString(2), rs.getString(3));
				last_item.setHarm_code(rs.getString(4));
				last_item.setConstant(rs.getInt(5) == 1);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return result;
	}

	public PropertyListItem getItemById(long item_id)
		 throws SQLException {
		Connection con = getConnection();

		PropertyListItem item = null;
		try {
			PreparedStatement ps = con.prepareStatement(GET_ITEM_BY_ID);
			ps.setLong(1, item_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
//				"SELECT list_id,id,lang,value,harm_code,isConst " +
//				"FROM prop_list_items " +
//				"WHERE id=? ";
				if (item == null) {
					// new item
					item = new PropertyListItem();
					item.setId(item_id);
				}
				item.setList_id(rs.getLong(1));
				item.setId(rs.getLong(2));
				String lang = rs.getString(3);
				String value = rs.getString(4);
//				System.out.println("found:" + lang + " :" + value);
				item.setString(lang, value);
				item.setHarm_code(rs.getString(5));
				item.setConstant(rs.getInt(6) == 1);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}

		return item;
	}

	public void deleteItem(long id)
		 throws SQLException {
		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(DELETE_ITEM);
			ps.setLong(1, id);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	public void insertItem(PropertyListItem item)
		 throws SQLException {

		if (item.getStrings() == null || item.getStrings().size() < 1) {
			return;
		}

		if (item.getId() == -1) {
			item.setId(db_token.getIdgenerators().nextVal(Constants.IDGEN_PROP_LIST_ITEMS));
		}

		Connection con = getConnection();

		try {
			PreparedStatement ps = con.prepareStatement(INSERT_ITEM);
			Iterator it = item.getStrings().keySet().iterator();
			while (it.hasNext()) {
				String lang = (String) it.next();
				String value = item.getString(lang);
				if (value == null) continue;
				ps.clearParameters();
				ps.setLong(1, item.getList_id());
				ps.setLong(2, item.getId());
				ps.setString(3, lang);
//				System.out.println("insert:["+lang+"]");
				ps.setString(4, value);
				if (item.getHarm_code() == null) {
					ps.setNull(5, Types.VARCHAR);
				} else {
					ps.setString(5, item.getHarm_code());
				}
				ps.setInt(6, item.isConstant() ? 1 : 0);
				ps.executeUpdate();
			}
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
	}

	public void insertOrUpdateItem(PropertyListItem item)
		 throws SQLException {
		if (item.getId() == -1) {
			insertItem(item);
		} else {
			deleteItem(item.getId());
			insertItem(item);
		}
	}

	public void update(PropertyListItem item)
		 throws SQLException {
		if (item.getId() == -1) {
			throw new SQLException("Trying to update a new property");
		}
		deleteItem(item.getId());
		insertItem(item);
	}

	public long getMaxListId()
		 throws SQLException {
		Connection con = getConnection();
		long result = 0;
		try {
			PreparedStatement ps = con.prepareStatement(GET_LAST_LIST_ID);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
		return result;
	}

	public long getMaxItemId()
		 throws SQLException {
		Connection con = getConnection();
		long result = 0;
		try {
			PreparedStatement ps = con.prepareStatement(GET_LAST_ITEM_ID);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				result = rs.getLong(1);
			}
			rs.close();
			ps.close();
		} catch (SQLException sqlEx) {
			throw sqlEx;
		} finally {
			con.close();
		}
		return result;
	}

	private static final String GET_LIST =
		 "SELECT id,name,dn_trl,lt_trl,it_trl " +
		 "FROM prop_lists " +
		 "WHERE name=?";
	private static final String GET_LIST_BY_ID =
		 "SELECT id,name,dn_trl,lt_trl,it_trl " +
		 "FROM prop_lists " +
		 "WHERE id=?";
	private static final String GET_ITEM_BY_ID =
		 "SELECT list_id,id,lang,value,harm_code,isConst " +
		 "FROM prop_list_items " +
		 "WHERE id=? ";
	private static final String GET_ITEMS_FOR_LIST =
		 "SELECT id,lang,value,harm_code,isConst " +
		 "FROM prop_list_items " +
		 "WHERE list_id=? " +
		 "ORDER BY id ASC";
	private static final String DELETE_ITEM =
		 "DELETE FROM prop_list_items WHERE id=?";
	private static final String INSERT_ITEM =
		 "INSERT INTO prop_list_items (list_id,id,lang,value,harm_code,isConst) " +
		 "VALUES (?,?,?,?,?,?)";
	private static final String GET_LAST_LIST_ID =
		 "SELECT max(id) FROM prop_lists";
	private static final String GET_LAST_ITEM_ID =
		 "SELECT max(id) FROM prop_list_items";
}
